package zhongbo.java.air.monitor.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import zhongbo.java.air.monitor.model.FlightActivityDaily;

import java.util.List;

/**
 * 说明：
 *
 * @author 钟波dellNotebook
 * @since 2025年04月08日 星期二 03:24:47
 */
@Mapper
public interface FlightActivityDailyMapper extends BaseMapper<FlightActivityDaily> {

    /**
     * 查询当天分组统计结果
     *
     * @param date 某一天的日期 如: 2025-04-08
     * @return 当天分组统计结果
     */
    @Select("SELECT \n" +
            "    a.icao_code AS icaoCode,\n" +
            "    MIN(a.timestamp) AS firstSeenTime,\n" +
            "    MAX(a.timestamp) AS lastSeenTime,\n" +
            "    DATE(MIN(a.timestamp)) AS date,\n" +
            "    (\n" +
            "        SELECT altitude \n" +
            "        FROM air_flight_path1 \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastAltitude," +
            "    (\n" +
            "        SELECT latitude \n" +
            "        FROM air_flight_path1 \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastLatitude,\n" +
            "    (\n" +
            "        SELECT longitude \n" +
            "        FROM air_flight_path1 \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastLongitude,\n" +
            "    (\n" +
            "        SELECT speed \n" +
            "        FROM air_flight_path1 \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastSpeed,\n" +
            "    (\n" +
            "        SELECT heading \n" +
            "        FROM air_flight_path1 \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastHeading,\n" +
            "    COUNT(*) AS occurrenceCount\n" +
            "FROM air_flight_path1 a\n" +
            "WHERE DATE(timestamp) = #{date}\n" +
            "GROUP BY icao_code;")
    List<FlightActivityDaily> selectDailyStats(String date);
}
